/**
 * 
 */
package fr.utbm.gi.in56.message;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import fr.utbm.gi.in56.ads.AdState;
import fr.utbm.gi.in56.ads.AdType;
import fr.utbm.gi.in56.database.DatabaseManager;
import fr.utbm.gi.in56.error.ErrorType;

/**
 * 
 *
 * @author Adrien Hoffer & Tarik Belabbas
 *
 */
public class MessageManager extends DatabaseManager
{
	/**
	 * Parameterized constructor.
	 * 
	 * @param _connection the connection to use to create a statement
	 * @throws SQLException if the connection cannot create a statement
	 */
	public MessageManager( Connection _connection )
	{
		super( _connection );
	}
	
	/**
	 * Sets the messages of an user as read
	 * 
	 * @param _userLogin the login of the user
	 */
	public void setMessagesAsRead( String _userLogin )
	{
		Statement statement = this.getNewStatement();
		
		try
		{
			statement.executeUpdate( "UPDATE messages SET is_read = '1'" + //$NON-NLS-1$
					" WHERE login_receiver = '" + _userLogin.replace( "'", "''" ) + "'" ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
	}
	
	/**
	 * Returns the inbox messages
	 * 
	 * @param _type : INBOX or SENT
	 * @param _userLogin the user login
	 * @return the inbox messages
	 */
	public List< Message > getMessages( String _userLogin, MessageType _type )
	{
		Statement statement = this.getNewStatement();
		List< Message > msgList = new LinkedList< Message >();
		
		String loginType = ""; //$NON-NLS-1$
		
		if( _type == MessageType.INBOX)
		{
			loginType = "login_receiver"; //$NON-NLS-1$
		}
		else
		{
			loginType = "login_sender"; //$NON-NLS-1$
		}
		
		try
		{
			ResultSet rst = statement.executeQuery( "SELECT messages.*," +  //$NON-NLS-1$
					"(SELECT ads_sale.title FROM ads_sale WHERE" +  //$NON-NLS-1$
					" ads_sale.id_ads_sale = messages.id_ad_sale) AS adSaleTitle," +  //$NON-NLS-1$
					" (SELECT ads_buy.title FROM ads_buy WHERE ads_buy.id_ads_buy =" +  //$NON-NLS-1$
					" messages.id_ad_buy) AS adBuyTitle FROM messages " + //$NON-NLS-1$
					"WHERE " + loginType + " = '" + _userLogin.replace( "'", "''" ) + "' ORDER BY date DESC"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$
			
			while( rst.next() )
			{
				String msg = rst.getString( "message" ); //$NON-NLS-1$
				String adBuyTitle = rst.getString( "adBuyTitle" ); //$NON-NLS-1$
				String adSaleTitle = rst.getString( "adSaleTitle" ); //$NON-NLS-1$
				
				boolean isRead = ( rst.getInt( "is_read" ) == 0 ) ? false : true; //$NON-NLS-1$
				
				if( msg == null)
				{
					msg = ""; //$NON-NLS-1$
				}
				
				if( adBuyTitle == null)
				{
					adBuyTitle = ""; //$NON-NLS-1$
				}
				
				if( adSaleTitle == null)
				{
					adSaleTitle = ""; //$NON-NLS-1$
				}
				
				msgList.add( new Message( rst.getInt( "id_messages" ), //$NON-NLS-1$
						rst.getString( "login_receiver" ), rst.getString( "login_sender" ), //$NON-NLS-1$ //$NON-NLS-2$
						rst.getString( "object" ), msg, rst.getDate( "date" ), //$NON-NLS-1$ //$NON-NLS-2$
						rst.getInt( "id_ad_sale" ), rst.getInt( "id_ad_buy" ), isRead, adBuyTitle, adSaleTitle ) ); //$NON-NLS-1$ //$NON-NLS-2$
			}
			
			rst.close();
		}
		catch( SQLException e )
		{
			e.printStackTrace();
		}
		return msgList;
	}
	
	/**
	 * Returns the number of unread messages of an user
	 * Method only necessary for an user
	 * 
	 * @param _login the login of the user
	 * @return the number of unread messages of the user
	 */
	public int getUnreadMessagesNb( String _login )
	{	
		int nbMsg = 0;
		
		try
		{
			Statement statement = this.getNewStatement();
			ResultSet rst 		= statement.executeQuery( "SELECT count(*) FROM messages " + //$NON-NLS-1$
					"WHERE login_receiver = '" + _login.replace( "'", "''" ) +  //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
					"' AND is_read = '" + 0 + "'" ); //$NON-NLS-1$ //$NON-NLS-2$
			
			if( rst.first() )
			{
				nbMsg = rst.getInt( 1 );
			}
			rst.close();
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return nbMsg;
	}
	
	/**
	 * Returns ErrorType.NONE if a message can be sent to the recipient about an ad.
	 * If the type of message is NEW then the recipient must have created the ad
	 * and be in open state. If the type of message is ANSWER then the sender
	 * must have received at least one message about the ad but the ad can be in
	 * any state.
	 * 
	 * @param _sender the sender of the message
	 * @param _recipient the potential recipient of the message
	 * @param _adId the ad id
	 * @param _adType the type of ad
	 * @param _msgType the type of message
	 * @return ErrorType.NONE if the message can be sent
	 */
	public ErrorType canSendMessageTo( String _sender, String _recipient,
			int _adId, AdType _adType, MessageType _msgType )
	{	
		
		Statement statement = this.getNewStatement();
		
		ErrorType result 	= ErrorType.NONE;
		
		String adType 		= _adType.getType();
		
		ResultSet rst 		= null;
		
		String recipient 	= new String();
		
		if( _adType == AdType.SELL )
		{
			recipient = "login_seller"; //$NON-NLS-1$
		}
		else
		{
			recipient = "login_buyer"; //$NON-NLS-1$
		}			
		
		try
		{
			rst = statement.executeQuery( "SELECT * FROM ads_" + //$NON-NLS-1$
					adType + " WHERE id_ads_" + adType + "='" + _adId + "'"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
			
			if ( rst.first() )
			{
				// If the message if an ad response
				if( _msgType == MessageType.NEW )
				{
					if ( rst.first() )
					{
						// Test if the ad is in an opened state
						
						if( AdState.values()[ rst.getInt( "state" ) ] != AdState.OPENED ) //$NON-NLS-1$
						{
							result = ErrorType.MESSAGE_AD_NOT_OPENED;
						}
						else
						{	
							// Test if the recipient is the ad poster
							if( rst.getString( recipient ).compareTo( _recipient ) != 0 )
							{
								result = ErrorType.MESSAGE_RECIPIENT_AND_AD_DONT_MATCH;
							}
						}
					}
				}
				else
				{
					//If the message is a basic answer
					if( _msgType == MessageType.ANSWER )
					{
						try
						{
							rst = statement.executeQuery( "SELECT * FROM messages WHERE " + //$NON-NLS-1$
									"login_receiver = '" + _sender.replace( "'", "''" ) + "' AND login_sender ='" +  //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
									_recipient.replace( "'", "''" ) + "'"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
							if( ! rst.first() )
							{
								result = ErrorType.MESSAGE_NOT_ALLOWED;
							}
						}
						catch ( SQLException e )
						{
							e.printStackTrace();
							result = ErrorType.DB_ERROR;
						}
					}
				}
			}
			else
			{
				result = ErrorType.MESSAGE_INVALID_AD;
			}
			
			rst.close();
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
			result = ErrorType.DB_ERROR;
		}
		return result;
	}
	
	/**
	 * Sends a message to a recipient about an announce.
	 * 
	 * @param _recipient the recipient of the message
	 * @param _adId the id of the ad
	 * @param _subject the subject of the add
	 * @param _message the message
	 * @param _sender the sender
	 * @param _adType the type of ad
	 * @return the type of error, ErroType.NONE if there is no error
	 */
	public ErrorType sendMessage( String _recipient, String _sender, int _adId, 
			String _subject, String _message, AdType _adType )
	{	
		Statement statement = this.getNewStatement();
		
		try
		{
			String sqlQuery = "INSERT INTO `messages` (`login_receiver`," + //$NON-NLS-1$
				"`login_sender`,`object`,`message`,`id_ad_" + _adType.getType() + "`)" +  //$NON-NLS-1$ //$NON-NLS-2$
				" VALUES ('" + _recipient.replace( "'", "''" ) + "','" + //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
				_sender.replace( "'", "''" ) + "','" + _subject.replace( "'", "''" ) + //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$
				"','" + _message.replace( "'", "''" ) + "','" + _adId + "')"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$
			
			statement.executeUpdate( sqlQuery );
			
			return ErrorType.NONE;
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
			return ErrorType.DB_ERROR;
		}
	}
}
